import requests
from lxml import etree
import pymysql
import time
def bigCity():

    url = "https://www.jianzhimao.com/ctrlcity/changeCity.html"
    #UA伪装
    headers = {
                 "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36"
             }
    #发送数据请求
    resp = requests.get(url=url,headers=headers)
    resp.encoding="utf-8"
    #提取页面数据
    page_city_text = resp.text
    tree = etree.HTML(page_city_text)
    #获取城市名称
    all_city_name = tree.xpath('//ul[@class="city_table"]/li/a/text()')
    #获取城市链接
    all_city_href = tree.xpath('//ul[@class="city_table"]/li/a/@href')
    #print(all_city_href)

    value = []
    keys = []
    # 遍历循环把链接添加到value里
    for href in all_city_href:
        value.append(href)
    # print(value)
    # 把名字添加到keys里
    for name in all_city_name:
        keys.append(name)
    # print(keys)
    dict0 = dict(zip(keys, value))
    print(dict0)

    # 连接数据库
    db = pymysql.connect(host='localhost', user='root', password='0830', database='moonlightcat_test')
    # 获取游标对象
    cursor = db.cursor()
    for i in dict0:
        # print(i)
        # print(dict[i])

        sql = "insert into city(name,href) values('%s','%s')" % (i, dict0[i])
        print(sql)
        cursor.execute(sql)
    try:
        # 执行sql
        # 提交
        db.commit()
    except Exception as e:
        # 事务回滚
        db.rollback()
        print(e)
    # 关闭
    db.close()





